import pymysql
# from .settings import mysql_host,mysql_db,mysql_user,mysql_passwd,mysql_port
class MenusqlPipeline(object):
    def open_spider(self,spider):
        host=spider.settings.get('MYSQL_HOST')
        port=spider.settings.get('MYSQL_POST')
        database=spider.settings.get('MYSQL_DATABASE')
        user=spider.settings.get('MYSQL_USER')
        password=spider.settings.get('MYSQL_PASSWORD')
        self.db_connect=pymysql.connect(host=host,port=port,database=database,user=user,password=password,charset='utf8')
        self.cursor=self.db_connect.cursor()
        print('数据库连接成功.....')

    def close_spider(self,spider):
        self.db_connect.commit()
        self.db_connect.close()

    def process_item(self,item,spider):
        self.insert_db(item)
        return item

    def insert_db(self, item):
        params=list()
        params.append(item['name'])
        params.append(item['step'])
        params.append(item['sugar'])
        params.append(item['energy'])
        params.append(item['fat'])
        params.append(item['material1'])
        params.append(item['material2'])
        params.append(item['needtime'])
        params.append(item['img'])
        params.append(item['level'])

        sql = "insert into cookbook(name,step,sugar,energy,fat,material1,material2,need_time,img_url,level) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        # 注意 params添加的顺序要和sql语句插入的顺序一致，否则会出现错位甚至插入失败的情况
        try:
            self.cursor.execute(sql,tuple(params))
            print("数据保存成功")
        except:
            print("数据保存失败")
